
USE [DBAObjects]
GO

-- The following code is basically tied to rights issues that I decided to look 
-- into after reading this article: 
-- http://www.sqlservercentral.com/articles/Administration/64974/
-- While it doesn't so much cover rights it got me thinking.  The code here is 
-- directly to resolve permissions issues that could cause logons to be denied
-- because the user doesn't have sufficient rights to log their logon.  This was
-- tested with a user that had ONLY a sql logon to the server and no other 
-- rights.  That user was unable to logon.  I have added a login called
-- AuditCredLogin and a user AuditCredUser, this user exists in both the Audit
-- and Repository databases.  This login has a long nasty password assembled 
-- from 2 GUIDs that are generated locally, the password is not displayed and 
-- does not need to be know, it is NEVER used to actually login to the server, 
-- therefore is disabled.

DECLARE @pwd VARCHAR(80)

-- Generate random LONG password, we don't need to know what it is..
SELECT @pwd = CONVERT( VARCHAR(38), NEWID() ) + ' - ' + CONVERT( VARCHAR(38), NEWID() )

IF NOT EXISTS ( SELECT 'X' FROM master.sys.server_principals WHERE name = 'AuditCredLogin' )
BEGIN -- Login doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE LOGIN [AuditCredLogin] WITH PASSWORD = '''
                 + @pwd
                 + ''', '
                 + 'DEFAULT_DATABASE = [tempdb], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF '
     EXEC ( @cmd )
END
GO

-- Permission needed for Logon Trigger.
DECLARE @cmd varchar(8000)

SELECT @cmd = 'USE [master]; GRANT VIEW SERVER STATE TO [AuditCredLogin];'
EXEC ( @cmd )
GO

-- Disable Logon
DECLARE @cmd varchar(8000)

SELECT @cmd = 'ALTER LOGIN [AuditCredLogin] DISABLE;'
EXEC ( @cmd )
GO

IF NOT EXISTS ( SELECT 'X' FROM sys.database_principals WHERE name = 'AuditCredUser' )
BEGIN -- User doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE USER [AuditCredUser] FROM LOGIN [AuditCredLogin]; '
     EXEC ( @cmd )
END
GO

-- One additional point here, if this database is to be available for logging by
-- all other databases this next line should be executed, it is currently not
-- included for security reasons.  However enabling guest access to the database
-- does NOT grant grant guest access to the server and the login must already
-- have been authenticated.  Do not under any circumstances grant the guest 
-- login access to the SQL Server!!! Currently the only object exposed to the 
-- guest USER is the dbo.InsEventLog sproc.  This may be independently verified
-- by creating a login with no explicit user permisisons in the new database and
-- logging is as that user and then by executing:
-- SELECT * FROM sys.objects
-- in the new database, only the dbo.InsEventLog sproc should be displayed.

--GRANT CONNECT TO guest
--GO
